package com.mrobuy.jdvc.service.impl;

import com.common.utils.AccountContextHolder;
import com.gavel.database.SQLExecutor;
import com.gavel.parser.MYMROSkuParser;
import com.gavel.parser.SkuItem;
import com.gavel.utils.StringUtils;
import com.google.common.collect.Lists;
import com.google.gson.Gson;
import com.google.gson.GsonBuilder;
import com.google.gson.reflect.TypeToken;
import com.jd.open.api.sdk.JdClient;
import com.jd.open.api.sdk.domain.supplier.ProductManagementService.response.update.JosStringDto;
import com.jd.open.api.sdk.request.supplier.VcItemNewProductCreateRequest;
import com.jd.open.api.sdk.response.supplier.VcItemNewProductCreateResponse;
import com.jd.service.JDIntroService;
import com.mrobuy.jd.JdClientFactory;
import com.mrobuy.jdvc.Result;
import com.mrobuy.jdvc.dao.*;
import com.mrobuy.jdvc.entity.*;
import com.mrobuy.jdvc.model.CategoryInfo;
import com.mrobuy.jdvc.model.ItemCondition;
import com.mrobuy.jdvc.service.*;
import com.mrobuy.type.ApplyState;
import com.mrobuy.type.ItemState;
import com.mrobuy.util.CodeEncoder;
import com.mrobuy.util.IdentifierGen;
import com.mrobuy.util.Pagination;
import org.apache.logging.log4j.util.Strings;
import org.apache.poi.ss.usermodel.FillPatternType;
import org.apache.poi.ss.usermodel.HorizontalAlignment;
import org.apache.poi.ss.usermodel.VerticalAlignment;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.ss.util.WorkbookUtil;
import org.apache.poi.xssf.usermodel.*;
import org.openxmlformats.schemas.spreadsheetml.x2006.main.CTTable;
import org.openxmlformats.schemas.spreadsheetml.x2006.main.CTTableColumn;
import org.openxmlformats.schemas.spreadsheetml.x2006.main.CTTableColumns;
import org.openxmlformats.schemas.spreadsheetml.x2006.main.CTTableStyleInfo;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.data.redis.core.RedisTemplate;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Service;
import org.springframework.web.servlet.mvc.method.annotation.SseEmitter;
import com.jd.open.api.sdk.request.supplier.VcItemOldProductUpdateRequest;
import com.jd.open.api.sdk.response.supplier.VcItemOldProductUpdateResponse;

import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;
import java.math.BigDecimal;
import java.math.RoundingMode;
import java.nio.file.Files;
import java.nio.file.Path;
import java.nio.file.Paths;
import java.nio.file.StandardCopyOption;
import java.util.*;
import java.util.concurrent.ConcurrentHashMap;

@Service("shelvesService")
public class ShelvesServiceImpl implements ShelvesService {

    private static Gson  GSON = new GsonBuilder().create();

    private static final String SALER_CODE = "shaoshihao";

    private static final String PURCHASER_CODE = "liujie192";

    private static Map<String, SseEmitter> sseCache = new ConcurrentHashMap<>();

    private static Logger LOG = LoggerFactory.getLogger(ShelvesService.class);

    @Autowired
    private JdbcTemplate jdbcTemplate;

    @Autowired
    private VCCatePropDao vcCatePropDao;

    @Autowired
    private VCCateExtPropDao vcCateExtPropDao;

    @Autowired
    private VCCateSalePropDao vcCateSalePropDao;

    @Autowired
    private VCCateBrandDao vcCateBrandDao;

    @Autowired
    private TaskDao taskDao;

    @Autowired
    private CommonDao commonDao;

    @Autowired
    private VCProductDao vcProductDao;

    @Autowired
    private SeachItemDao seachItemDao;

    @Autowired
    private HtmlCacheDao htmlCacheDao;

    @Autowired
    private ItemService itemService;

    @Autowired
    private VCCateFutureDao vcCateFutureDao;

    @Autowired
    private ItemPropService itemPropService;

    @Autowired
    private IntroBuilderService introBuilderService;

    @Autowired
    private JDIntroService jdIntroService;

    @Autowired
    private ItemAttrService itemAttrService;

    @Autowired
    private VCCategoryService vcCategoryService;

    @Autowired
    private VCProductApplyDao vcProductApplyDao;

    @Autowired
    private RedisTemplate<String, Object> redisTemplate;

    @Override
    public Result skuimport(String taskid, String fromid) {

        Result result = new Result();

       Task task = taskDao.load(fromid);
       if ( task==null ) {
           result.setSuccess(false);
           result.setMsg("任务[" + fromid + "]不存在");
           return result;
       }


       Map<String, VCProduct> exist = new ConcurrentHashMap<>();
       VCProduct condition = new VCProduct();
        condition.setTaskid(taskid);
        Pagination pagination = vcProductDao.load(0, Integer.MAX_VALUE, condition);
        if ( pagination!=null && pagination.getData()!=null ) {
            for (Object ele : pagination.getData()) {
                VCProduct vcProduct = (VCProduct)ele;
                exist.put(vcProduct.getOriginalCode(), vcProduct);
            }
        }

        List<SearchItem> items  =  seachItemDao.query(task.getId());

       if ( items!=null ) {


           MYMROSkuParser parser = new MYMROSkuParser();

           for (SearchItem item : items) {

               pushEvent(item);

               if ( exist.containsKey(item.getCode()) ) {
                   continue;
               }

               List<Item> skuItems = new ArrayList<>();

               if ( "g".equalsIgnoreCase(item.getType()) ) {
                   List<Item> _items = itemService.queryItems(item.getCode(), task.getType());
                   if ( _items!=null ) {
                       skuItems.addAll(_items);
                   }
               } else {
                   Item _item = itemService.queryItem(item.getCode(), task.getType());
                   if ( _item!=null ) {
                       skuItems.add(_item);
                   }
               }

               if ( skuItems.size()==0 ) {
                   LOG.warn("[" + item.getCode() + ": " + item.getTitle() + "]没有对应Item信息!");
                   continue;
               }

               try {
                   buildProduct(item.getTitle(), skuItems, taskid);
               } catch (Exception e) {
                    e.printStackTrace();
               }
           }

       }


        result.setSuccess(true);
        return result;
    }

    @Override
    public SseEmitter buildSseEmitter(String id) throws IOException {
        // 超时时间设置为3s，用于演示客户端自动重连
        SseEmitter sseEmitter = new SseEmitter(3600_000L);
        // 设置前端的重试时间为1s
        sseEmitter.send(SseEmitter.event().reconnectTime(1000).data("连接成功"));
        sseCache.put(id, sseEmitter);

        sseEmitter.onTimeout(() -> {
            System.out.println(id + "超时");
            sseCache.remove(id);
        });
        sseEmitter.onCompletion(() -> System.out.println("完成！！！"));

        return sseEmitter;
    }

    @Override
    public void shelves(String[] ids) {

        if ( ids==null || ids.length==0 ) {
            return;
        }

        for (String id : ids) {

            VCProduct vcProduct = vcProductDao.load(id);
            if ( vcProduct==null ) {
                LOG.error("[ID: " + id + "]VCProduct记录未找到!");
                continue;
            }

            try {
                shelves(vcProduct);
            } catch (Exception e){
                e.printStackTrace();
                LOG.error("[ID: " + id + "][" + vcProduct.getName() + "]上架异常: " + e.getMessage());
                throw new RuntimeException("[ID: " + id + "][" + vcProduct.getName() + "]上架异常: " + e.getMessage());
            }

        }

    }

    @Override
    public Pagination loadTasks(int start, int length, VCShelvesTask condition) {

        Pagination pagination = new Pagination();

        List<Object> params = new ArrayList<>();

        StringBuilder whereCond = new StringBuilder(" where 1 =1 ");
        if ( condition.getName()!=null && condition.getName().trim().length() > 0 ) {
            whereCond.append("  and NAME like ? ");
            params.add("%" + condition.getName().trim() + "%");
        }

        if ( condition.getState()!=null && condition.getState().trim().length() > 0 ) {
            whereCond.append(" and STATE = ? ");
            params.add(condition.getState().trim() );
        }

        if ( condition.getTitle()!=null && condition.getTitle().trim().length() > 0 ) {
            whereCond.append(" and task.TITLE like ? ");
            params.add("%" + condition.getTitle().trim() + "%");
        }

        try {
            int count = jdbcTemplate.queryForObject("select count(1) from VC_SHELVES_TASK left join task on task.ID = vc_shelves_task.SOURCE " + whereCond.toString(), params.toArray(new Object[params.size()]), Integer.class);

            params.add(start);
            params.add(length);
            List<VCShelvesTask> list = jdbcTemplate.query("select VC_SHELVES_TASK.*, task.TITLE, task.URL, task.STATUS, task.TYPENAME from VC_SHELVES_TASK left join task on task.ID = vc_shelves_task.SOURCE " + whereCond.toString() + " limit ?, ?", params.toArray(new Object[params.size()]), new BeanPropertyRowMapper(VCShelvesTask.class));


            pagination.setRecordsTotal(count);
            pagination.setRecordsFiltered(count);
            pagination.setData(list);
        } catch (Exception e) {
            LOG.error(e.getMessage());
        }

        return pagination;

    }

    @Override
    public Result insert(VCShelvesTask record) {
        Result result = new Result();

        if ( record==null ||  record.getName()==null ||  record.getName().trim().length()==0 ) {
            result.setSuccess(false);
            result.setMsg("任务名称不能为空");
            return result;
        }

        if ( record.getSource()==null ||  record.getSource().trim().length()==0 ) {
            result.setSuccess(false);
            result.setMsg("请选择需要上架的采集任务");
            return result;
        }

        try {
            VCShelvesTask exist =  (VCShelvesTask)jdbcTemplate.queryForObject("select * from VC_SHELVES_TASK where NAME = ? ", new Object[]{record.getName().trim()},  new BeanPropertyRowMapper(VCShelvesTask.class));
            if ( exist!=null ) {
                result.setSuccess(false);
                result.setMsg("任务[" + record.getName() + "]已经存在, 请修改任务名称");
                return result;
            }
        } catch (Exception e) {
            LOG.error(e.getMessage(), e.getCause());
        }

        record.setId(IdentifierGen.uuid());
        record.setState("0");
        record.setCreatetime(Calendar.getInstance().getTime());
        record.setEnable("N");

        int cnt =  commonDao.insert(record);
        if ( cnt==0 ) {
            result.setSuccess(false);
            result.setMsg("任务[" + record.getTitle() + "]插入失败");
            return result;
        }

        result.setSuccess(true);
        return result;
    }

    @Override
    public VCShelvesTask loadTask(String id) {
        try {
            return jdbcTemplate.queryForObject("select * from  VC_SHELVES_TASK where ID = ? ", new Object[]{id}, new BeanPropertyRowMapper<>(VCShelvesTask.class));
        } catch (Exception e) {
            LOG.error(e.getMessage(), e.getCause());
        }
        return null;
    }

    /**
     *  导出品牌类目映射
     * @param ouputStream
     * @param task
     */
    @Override
    public void exportBrandcateMapping(OutputStream ouputStream, VCShelvesTask task) {

        XSSFWorkbook wb = new XSSFWorkbook();


        try {

            {
                String safeName = WorkbookUtil.createSafeSheetName("品牌类目映射");
                XSSFSheet sheet = wb.createSheet(safeName);

                List<VCShelvesItem>  vcShelvesItems = jdbcTemplate.query("select * from VC_SHELVES_ITEM where TASKID = ? ", new Object[]{task.getId()}, new BeanPropertyRowMapper<>(VCShelvesItem.class));

                Map<String,  VCShelvesItem> map = new HashMap<>();
                if ( vcShelvesItems!=null ) {
                    for (VCShelvesItem vcShelvesItem : vcShelvesItems) {
                        map.put(vcShelvesItem.getProductcode(), vcShelvesItem);
                    }
                }

                if ( map.size() > 0 ) {
                    int rowNum = 0;

                    XSSFRow dataRow = sheet.createRow(rowNum++);

                    dataRow.createCell(0).setCellValue("编码");
                    dataRow.createCell(1).setCellValue("名称");
                    dataRow.createCell(2).setCellValue("1级类目");
                    dataRow.createCell(3).setCellValue("2级类目");
                    dataRow.createCell(4).setCellValue("3级类目");
                    dataRow.createCell(5).setCellValue("4级类目");
                    dataRow.createCell(6).setCellValue("品牌");

                    dataRow.createCell(7).setCellValue("上架类目");
                    dataRow.createCell(8).setCellValue("上架类目ID");
                    dataRow.createCell(9).setCellValue("上架品牌");
                    dataRow.createCell(10).setCellValue("上架品牌ID");


                    for (VCShelvesItem shelvesItem : map.values()) {
                        dataRow = sheet.createRow(rowNum++);

                        XSSFCell dataCell = dataRow.createCell(0);
                        dataCell.setCellValue(shelvesItem.getProductcode());

                        dataCell = dataRow.createCell(1);
                        dataCell.setCellValue(shelvesItem.getProductname());

                        dataCell = dataRow.createCell(2);
                        dataCell.setCellValue(shelvesItem.getCate1());

                        dataCell = dataRow.createCell(3);
                        dataCell.setCellValue(shelvesItem.getCate2());

                        dataCell = dataRow.createCell(4);
                        dataCell.setCellValue(shelvesItem.getCate3());

                        dataCell = dataRow.createCell(5);
                        dataCell.setCellValue(shelvesItem.getCate4());

                        dataCell = dataRow.createCell(6);
                        dataCell.setCellValue(shelvesItem.getBrand());

                        dataCell = dataRow.createCell(7);
                        dataCell.setCellValue(shelvesItem.getCidName1());

                        dataCell = dataRow.createCell(8);
                        dataCell.setCellValue(shelvesItem.getCid1());

                        dataCell = dataRow.createCell(9);
                        dataCell.setCellValue(shelvesItem.getBrandName());

                        dataCell = dataRow.createCell(10);
                        dataCell.setCellValue(shelvesItem.getBrandid());
                    }
                }

                applyTableStyle(sheet, XSSFBuiltinTableStyle.TableStyleMedium13, "A1", "K" + (map==null ? 1 : map.size()+1), 11);
//
                for (int i = 0; i < 11; i++) {
                    sheet.autoSizeColumn(i);
                }
            }

            {
                String safeName = WorkbookUtil.createSafeSheetName("JD已授权类目品牌");
                XSSFSheet sheet = wb.createSheet(safeName);

                int rowNum = 0;
                XSSFRow header = sheet.createRow(rowNum++);
                String[] columnNames = {"上架类目", "上架类目ID", "上架品牌", "上架品牌ID"};
                for (int i = 0; i < 4 ; i++) {
                    header.createCell(i).setCellValue(columnNames[i]);
                }



                List<VCCateBrand>  cateBrands = vcCateBrandDao.available(AccountContextHolder.vendorCode());
                if ( cateBrands!=null ) {
                    for (VCCateBrand cateBrand : cateBrands) {
                        XSSFRow dataRow = sheet.createRow(rowNum++);

                        XSSFCell dataCell = dataRow.createCell(0);
                        dataCell.setCellValue(cateBrand.getCatename());

                        dataCell = dataRow.createCell(1);
                        dataCell.setCellValue(cateBrand.getCateid());

                        dataCell = dataRow.createCell(2);
                        dataCell.setCellValue(cateBrand.getName());

                        dataCell = dataRow.createCell(3);
                        dataCell.setCellValue(cateBrand.getBrandid());
                    }
                }

//                applyTableStyle(sheet, XSSFBuiltinTableStyle.TableStyleMedium13, "A1", "D" + (cateBrands==null ? 1 : cateBrands.size()+1), 4);
//
                for (int i = 0; i < 4; i++) {
                    sheet.autoSizeColumn(i);
                }
            }

            wb.write(ouputStream);
        } catch (Exception e) {

        } finally {
            try {
                wb.close();
            } catch (Exception e) {

            }
        }
    }

    /**
     * 从流中读取类目品牌映射配置
     * @param inputStream
     * @param id
     */
    @Override
    public void importCateBrandMapping(InputStream inputStream, String id) throws Exception {

        XSSFWorkbook wb = new XSSFWorkbook(inputStream);


        try {
            String safeName = WorkbookUtil.createSafeSheetName("品牌类目映射");
            XSSFSheet sheet = wb.getSheet(safeName);

            int maxRow = sheet.getLastRowNum();

            for (int row = 1; row <= maxRow; row++) {

                XSSFRow sheetRow = sheet.getRow(row);
                if ( sheetRow==null ) {
                    continue;
                }

                XSSFCell cell = sheetRow.getCell(0);
                if ( cell==null || StringUtils.isBlank(cell.getStringCellValue()) ) {
                    continue;
                }

                XSSFCell cateName = sheetRow.getCell(7);
                XSSFCell cateId = sheetRow.getCell(8);
                XSSFCell brandName = sheetRow.getCell(9);
                XSSFCell brandId = sheetRow.getCell(10);

                jdbcTemplate.update("update VC_SHELVES_ITEM set CID1 = ?, CIDNAME1 = ?, BRANDID = ?,  BRANDNAME = ? where TASKID = ? and PRODUCTCODE = ? ",
                        getValue(cateId), getValue(cateName), getValue(brandId),  getValue(brandName), id, getValue(cell));

            }
        } finally {
            wb.close();
        }

    }

    /**
     * 商品属性设置
     *  @param inputStream
     * @param taskid
     * @param originalFilename
     */
    @Override
    public void importAttrSetting(InputStream inputStream, String taskid, String originalFilename) throws Exception {

        if ( StringUtils.isBlank(taskid) ) {
            throw new RuntimeException("参数[id]不能为空");
        }


        VCShelvesTask task = jdbcTemplate.queryForObject("select * from VC_SHELVES_TASK where ID = ? ", new Object[]{taskid}, new BeanPropertyRowMapper<>(VCShelvesTask.class));
        if ( task==null  ) {
            LOG.warn("没有对应的上架任务[ID: " + taskid + "].");
            return;
        }

        Path path = Paths.get("./attr",  originalFilename);
        if ( !path.getParent().toFile().exists() ) {
            path.getParent().toFile().mkdirs();
        }
        Files.copy(inputStream, path, StandardCopyOption.REPLACE_EXISTING);
        task.setAttrfile(path.toFile().getAbsolutePath());
        commonDao.update(task);


        List<VCShelvesItem>  vcShelvesItems = jdbcTemplate.query("select * from VC_SHELVES_ITEM where TASKID = ? ", new Object[]{taskid}, new BeanPropertyRowMapper<>(VCShelvesItem.class));
        if ( vcShelvesItems==null || vcShelvesItems.size()==0 ) {
            LOG.warn("没有上架任务[ID: " + taskid + "]对应的SKU.");
            return;
        }


        Map<String,   List<VCShelvesItem>> map = new ConcurrentHashMap<>();
        if ( vcShelvesItems!=null ) {
            for (VCShelvesItem vcShelvesItem : vcShelvesItems) {
                String cateName = vcShelvesItem.getCidName1();
                if ( StringUtils.isBlank(cateName) ) {
                    continue;
                }

                String key = StringUtils.trim(cateName);
                List<VCShelvesItem> list =  map.get(key);
                if ( list==null ) {
                    list = new ArrayList<>();
                    map.put(key, list);
                }
                list.add(vcShelvesItem);
            }
        }

        if ( map.size()==0 ) {
            LOG.warn("上架任务[ID: " + taskid + "]没有设置好类目的SKU.");
            return;
        }


        XSSFWorkbook wb = new XSSFWorkbook(path.toFile());
        int sheetNum =  wb.getNumberOfSheets();
        try {
            for (int i = 0; i < sheetNum; i++) {
                XSSFSheet sheet = wb.getSheetAt(i);
                String sheetName = sheet.getSheetName();
                if ( !map.containsKey(sheetName) ) {
                    LOG.warn("[Sheet: " + sheetName + "]Excel中没有对应数据");
                    continue;
                }

                List<VCShelvesItem> list =  map.get(sheetName);
                System.out.println("[Sheet: " + sheetName + "][Cnt: " + sheet.getLastRowNum() + "]" + list.size());




                String purchase = getValue(sheet.getRow(2).getCell(2), "");
                String sale = getValue(sheet.getRow(2).getCell(4), "");

                if ( StringUtils.isNotBlank(purchase)
                        ||  StringUtils.isNotBlank(sale) ) {
                    // 更新 采销员  信息
                    try {
                        VCShelvesItem shelvesItem = list.get(0);
                        VCPurchaseSale purchaseSale = jdbcTemplate.queryForObject("select * from VC_PURCHASE_SALE where CATE3 = ?", new BeanPropertyRowMapper<>(VCPurchaseSale.class), shelvesItem.getCidName1());
                        if ( purchaseSale==null ) {
                            purchaseSale = new VCPurchaseSale();
                            purchaseSale.setId(Math.abs(shelvesItem.getCidName1().hashCode()) + "-");
                            purchaseSale.setCid(shelvesItem.getCid1());
                            purchaseSale.setCate3(shelvesItem.getCidName1());

                            if ( StringUtils.isNotBlank(purchase) ) {
                                purchaseSale.setPurchasercode(purchase);
                            }

                            if ( StringUtils.isNotBlank(sale) ) {
                                purchaseSale.setSalercode(sale);
                            }

                            commonDao.insert(purchaseSale);
                        } else {
                            if ( StringUtils.isNotBlank(purchase) ) {
                                purchaseSale.setPurchasercode(purchase);
                            }

                            if ( StringUtils.isNotBlank(sale) ) {
                                purchaseSale.setSalercode(sale);
                            }
                            commonDao.update(purchaseSale);
                        }
                    } catch (Exception e){
                        LOG.error(e.getMessage(), e.getCause());
                    }
                }


                Map<String, Integer> attrcodeColumnMap = new HashMap<>();
                XSSFRow codeHiddenRow = sheet.getRow(0);
               int colunmCnt = codeHiddenRow.getLastCellNum();
                for (int j = 14; j < colunmCnt; j++) {
                    String code = getValue(codeHiddenRow.getCell(j));
                    if ( StringUtils.isNotBlank(code) ) {
                        attrcodeColumnMap.put(code, j);
                    }
                }


                Map<String, VCShelvesItem> codeIdMap = new ConcurrentHashMap<>();
                for (VCShelvesItem shelvesItem : list) {
                    codeIdMap.put(shelvesItem.getSkucode(), shelvesItem);
                }

                int rowCnt = sheet.getLastRowNum();
                for (int j = 4; j <= rowCnt; j++) {
                    XSSFRow rowData = sheet.getRow(j);
                    if ( rowData==null ) {
                        continue;
                    }

                    String code = getValue(rowData.getCell(0));
                    if ( StringUtils.isBlank(code) || !codeIdMap.containsKey(code) ) {
                        LOG.warn("[Row: {}][Code: {}]未匹配到ITEM记录", j, code);
                        continue;
                    }


                    VCShelvesItem exist = codeIdMap.get(code);
                    VCShelvesItem shelvesItem = new VCShelvesItem(exist.getId());

                    shelvesItem.setEnable(getValue(rowData.getCell(6), "N"));
                    shelvesItem.setMarketPrice(getValue(rowData.getCell(7), shelvesItem.getMarketPrice()));
                    shelvesItem.setPurchasePrice(getValue(rowData.getCell(8), shelvesItem.getPurchasePrice()));
                    shelvesItem.setMemberPrice(getValue(rowData.getCell(9), shelvesItem.getMemberPrice()));

                    shelvesItem.setLength(getValue(rowData.getCell(10), shelvesItem.getLength()));
                    shelvesItem.setWidth(getValue(rowData.getCell(11), shelvesItem.getWidth()));
                    shelvesItem.setHeight(getValue(rowData.getCell(12), shelvesItem.getHeight()));
                    shelvesItem.setWeight(getValue(rowData.getCell(13), shelvesItem.getWidth()));


                    Map<String, Integer> _attrcodeColumnMap = new ConcurrentHashMap<>();
                    _attrcodeColumnMap.putAll(attrcodeColumnMap);

                    Integer dim1Col = _attrcodeColumnMap.remove("1");
                    if ( dim1Col!=null ) {
                        shelvesItem.setDim1Sort("1");
                        shelvesItem.setDim1Val(getValue(rowData.getCell(dim1Col)));
                    }

                    Integer dim2Col = _attrcodeColumnMap.remove("2");
                    if ( dim1Col!=null ) {
                        shelvesItem.setDim2Sort("1");
                        shelvesItem.setDim2Val(getValue(rowData.getCell(dim2Col)));
                    }

                    for (String propid : _attrcodeColumnMap.keySet()) {
                        System.out.println("[Prop: " + propid  + "]" + getValue(rowData.getCell(_attrcodeColumnMap.get(propid))));
                        // vcCategoryService.commit(exist, propid, getValue(rowData.getCell(_attrcodeColumnMap.get(propid))));
                        redisTemplate.opsForHash().put(shelvesItem.getId() + "_attr", propid, getValue(rowData.getCell(_attrcodeColumnMap.get(propid))));

                    }



                    commonDao.update(shelvesItem, false);

                    System.out.println("Process: " + rowCnt + "/" + j);
                }

            }
        } finally {
            wb.close();
        }

    }

    /**
     * 商品属性文件生成
     * @param ouputStream
     * @param task
     */
    @Override
    public void exportAttrSetting(OutputStream ouputStream, VCShelvesTask task) {

        XSSFWorkbook wb = new XSSFWorkbook();
        try {

            List<VCShelvesItem>  vcShelvesItems = jdbcTemplate.query("select * from VC_SHELVES_ITEM where TASKID = ? ", new Object[]{task.getId()}, new BeanPropertyRowMapper<>(VCShelvesItem.class));

            Map<String,   List<VCShelvesItem>> map = new HashMap<>();
            if ( vcShelvesItems!=null ) {
                for (VCShelvesItem vcShelvesItem : vcShelvesItems) {
                    String cateName = vcShelvesItem.getCidName1();
                    if ( StringUtils.isBlank(cateName) ) {
                        cateName = "未设置类目SKU";
                    }

                    String key = StringUtils.trim(cateName);
                    List<VCShelvesItem> list =  map.get(key);
                    if ( list==null ) {
                        list = new ArrayList<>();
                        map.put(key, list);
                    }
                    list.add(vcShelvesItem);
                }
            }

            if ( map.size() > 0 ) {
                for (Map.Entry<String, List<VCShelvesItem>> entry : map.entrySet()) {
                    String safeName = WorkbookUtil.createSafeSheetName(entry.getKey());
                    XSSFSheet sheet = wb.createSheet(safeName);

                    int rowNum = 0;

                    XSSFRow codeHiddenRow = sheet.createRow(rowNum++);
                    codeHiddenRow.setZeroHeight(true);
                    XSSFRow nameHiddenRow = sheet.createRow(rowNum++);
                    nameHiddenRow.setZeroHeight(true);

                    XSSFRow head1Line = sheet.createRow(rowNum++);
                    XSSFRow head2Line = sheet.createRow(rowNum++);

                    head2Line.createCell(0).setCellValue("SKU编码");
                    head2Line.createCell(1).setCellValue("SKU名称");
                    head2Line.createCell(2).setCellValue("制造商型号");
                    head2Line.createCell(3).setCellValue("价格");
                    head2Line.createCell(4).setCellValue("预计发货日");

                    head2Line.createCell(6).setCellValue("是否上架");
                    head2Line.createCell(7).setCellValue("市场价");
                    head2Line.createCell(8).setCellValue("采购价");
                    head2Line.createCell(9).setCellValue("京东价");

                    nameHiddenRow.createCell(10).setCellValue("长度");
                    head2Line.createCell(10).setCellValue("长(mm)");

                    nameHiddenRow.createCell(11).setCellValue("宽度");
                    head2Line.createCell(11).setCellValue("宽(mm)");

                    nameHiddenRow.createCell(12).setCellValue("高度");
                    head2Line.createCell(12).setCellValue("高(mm)");

                    nameHiddenRow.createCell(13).setCellValue("重量");
                    head2Line.createCell(13).setCellValue("重量(KG)");



                    int column = 15;
                    VCShelvesItem shelvesItem = entry.getValue().get(0);
                    if ( StringUtils.isNotBlank(shelvesItem.getCid1()) ) {
                        String cid1 = shelvesItem.getCid1();

                        // 判断是否盖亚类目
                        VCFeature vcFeature = null;
                        try {
                            vcFeature = jdbcTemplate.queryForObject("select * from VC_FEATURE where CID = ?", new BeanPropertyRowMapper<>(VCFeature.class), cid1);
                            head1Line.createCell(0).setCellValue("盖亚类目: " + vcFeature.getGaea());
                            XSSFCellStyle cellStyle = wb.createCellStyle();
                            cellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);    //设置填充方案
                            cellStyle.setFillForegroundColor((new XSSFColor(new java.awt.Color(255, 0, 0))));  //设置填充颜色
                            head1Line.getCell(0).setCellStyle(cellStyle);   //celltop为具体需要设置的单元格
                        } catch (Exception e){
                            LOG.error(e.getMessage(), e.getCause());
                        }

                        head1Line.createCell(1).setCellValue("采购员: ");
                        head1Line.createCell(3).setCellValue("销售员: ");
                        VCPurchaseSale purchaseSale = null;
                        try {
                            purchaseSale = jdbcTemplate.queryForObject("select * from VC_PURCHASE_SALE where CATE3 = ?", new BeanPropertyRowMapper<>(VCPurchaseSale.class), shelvesItem.getCidName1());
                            head1Line.createCell(2).setCellValue(purchaseSale.getPurchasercode());
                            head1Line.createCell(4).setCellValue(purchaseSale.getSalercode());
                        } catch (Exception e){
                            LOG.error(e.getMessage(), e.getCause());
                        }

                        Map<String, String> types = new ConcurrentHashMap<>();
                        types.put("1", "-[单选]");
                        types.put("2", "-[复选]");
                        types.put("4", "-[时间年]");
                        types.put("5", "-[时间月]");
                        types.put("6", "-[时间日]");
                        types.put("7", "-[数值 ,jos自定义值(老逻辑展现形式)]");
                        types.put("8", "-[单选 + 文本]");

                        XSSFCellStyle cellStyle = wb.createCellStyle();
                        cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);//垂直bai
                        cellStyle.setAlignment(HorizontalAlignment.CENTER);//水平du
                        cellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);    //设置填充方案
                        cellStyle.setFillForegroundColor((new XSSFColor(new java.awt.Color(199, 255, 199))));  //设置填充颜色

                        // 规格参数
                        List<VCCategoryProp> categoryProps = vcCatePropDao.load(cid1);
                        if ( categoryProps != null && categoryProps.size() > 0 ) {
                            head1Line.createCell(column).setCellValue("[规格参数]");
                            if ( categoryProps.size() > 1 ) {
                                CellRangeAddress region = new CellRangeAddress(head1Line.getRowNum(), head1Line.getRowNum(), column, column+categoryProps.size() -1 );
                                sheet.addMergedRegion(region);
                            }
                            head1Line.getCell(column).setCellStyle(cellStyle);   //celltop为具体需要设置的单元格

                            for (VCCategoryProp categoryProp : categoryProps) {
                                codeHiddenRow.createCell(column).setCellValue(categoryProp.getPropid());
                                nameHiddenRow.createCell(column).setCellValue(categoryProp.getPropname());
                                head2Line.createCell(column++).setCellValue(categoryProp.getPropname() + types.getOrDefault(StringUtils.trim(categoryProp.getInputtype()), ""));
                            }
                            column++;
                        }


                        // 扩展属性
                        List<VCCategoryExtProp> cateExtProps = vcCateExtPropDao.load(shelvesItem.getCid1());
                        if ( cateExtProps != null && cateExtProps.size() > 0 ) {
                            head1Line.createCell(column).setCellValue("[扩展属性]");
                            if ( cateExtProps.size() > 1 ) {
                                CellRangeAddress region = new CellRangeAddress(head1Line.getRowNum(), head1Line.getRowNum(), column, column+cateExtProps.size() -1 );
                                sheet.addMergedRegion(region);
                            }
                            head1Line.getCell(column).setCellStyle(cellStyle);   //celltop为具体需要设置的单元格

                            for (VCCategoryExtProp categoryExtProp : cateExtProps) {
                                codeHiddenRow.createCell(column).setCellValue(categoryExtProp.getAttId());
                                nameHiddenRow.createCell(column).setCellValue(categoryExtProp.getName());
                                head2Line.createCell(column++).setCellValue(categoryExtProp.getName() + types.getOrDefault(StringUtils.trim(categoryExtProp.getInputType()), ""));
                            }
                            column++;
                        }


                        // 销售属性
                        List<VCCategorySaleProp> saleProps = vcCateSalePropDao.load(shelvesItem.getCid1());
                        if ( saleProps != null && saleProps.size() > 0 ) {
                            head1Line.createCell(column).setCellValue("[销售属性]");
                            if ( saleProps.size() > 1 ) {
                                CellRangeAddress region = new CellRangeAddress(head1Line.getRowNum(), head1Line.getRowNum(), column, column+saleProps.size() -1 );
                                sheet.addMergedRegion(region);
                            }
                            head1Line.getCell(column).setCellStyle(cellStyle);   //celltop为具体需要设置的单元格

                            for (VCCategorySaleProp categorySaleProp : saleProps) {
                                codeHiddenRow.createCell(column).setCellValue(categorySaleProp.getDim());
                                nameHiddenRow.createCell(column).setCellValue(categorySaleProp.getName());
                                head2Line.createCell(column++).setCellValue(categorySaleProp.getName());
                            }
                            column++;
                        }

                        for (VCCategoryProp categoryProp : categoryProps) {
                            if ( "1".equalsIgnoreCase(categoryProp.getInputtype())
                                    || "2".equalsIgnoreCase(categoryProp.getInputtype()) ) {

                                head1Line.createCell(column).setCellValue("规格参数选项");
                                head2Line.createCell(column).setCellValue("[规格参数]" + categoryProp.getPropname());

                                List<VCPropValue> list = jdbcTemplate.query("select  * from VC_PROP_VALUE where PROPID = ? ", new Object[]{categoryProp.getPropid()}, new BeanPropertyRowMapper(VCPropValue.class));

                                int rowIndex = head2Line.getRowNum()+1;
                                for (VCPropValue vcPropValue : list) {
                                    XSSFRow row = sheet.getRow(rowIndex);
                                    if ( row==null ) {
                                        row = sheet.createRow(rowIndex);
                                    }
                                    row.createCell(column).setCellValue(vcPropValue.getValueName());
                                    rowIndex++;
                                }

                                column++;
                            }
                        }

                        column++;
                        for (VCCategoryExtProp categoryExtProp : cateExtProps) {
                            if ( "1".equalsIgnoreCase(categoryExtProp.getInputType())
                                    || "2".equalsIgnoreCase(categoryExtProp.getInputType()) ) {

                                head1Line.createCell(column).setCellValue("扩展属性选项");
                                head2Line.createCell(column).setCellValue("[扩展属性]" + categoryExtProp.getName());

                                List<VCExtPropValue> list = jdbcTemplate.query("select  * from VC_EXTPROP_VALUE where PROPID = ? ", new Object[]{categoryExtProp.getAttId()}, new BeanPropertyRowMapper(VCExtPropValue.class));

                                int rowIndex = head2Line.getRowNum()+1;
                                for (VCExtPropValue extPropValue : list) {
                                    XSSFRow row = sheet.getRow(rowIndex);
                                    if ( row==null ) {
                                        row = sheet.createRow(rowIndex);
                                    }
                                    row.createCell(column).setCellValue(extPropValue.getValueName());
                                    rowIndex++;
                                }

                                column++;
                            }
                        }

                    }


                    List<VCShelvesItem> shelvesItems = entry.getValue();
                    shelvesItems.sort(new Comparator<VCShelvesItem>() {
                        @Override
                        public int compare(VCShelvesItem o1, VCShelvesItem o2) {
                            return o1.getSkucode().compareTo(o2.getSkucode());
                        }
                    });
                    for (VCShelvesItem _shelvesItem : shelvesItems) {

                        XSSFRow dataRow = sheet.getRow(rowNum);
                        if ( dataRow==null ) {
                            dataRow = sheet.createRow(rowNum);
                        }
                        rowNum++;

                        dataRow.createCell(0).setCellValue(_shelvesItem.getSkucode());
                        dataRow.createCell(1).setCellValue(_shelvesItem.getSkuname());
                        dataRow.createCell(2).setCellValue(_shelvesItem.getModel());
                        dataRow.createCell(3).setCellValue(_shelvesItem.getPrice());
                        dataRow.createCell(4).setCellValue(_shelvesItem.getDeliverydate());

                        dataRow.createCell(6).setCellValue("Y");
                        dataRow.createCell(7).setCellValue(_shelvesItem.getMarketPrice());
                        dataRow.createCell(8).setCellValue(_shelvesItem.getPurchasePrice());
                        dataRow.createCell(9).setCellValue(_shelvesItem.getMemberPrice());


                        dataRow.createCell(10).setCellValue(_shelvesItem.getLength());
                        dataRow.createCell(11).setCellValue(_shelvesItem.getWidth());
                        dataRow.createCell(12).setCellValue(_shelvesItem.getHeight());
                        dataRow.createCell(13).setCellValue(_shelvesItem.getWeight());


                        Map<String, String>  attrs = GSON.fromJson(_shelvesItem.getAttrs(), Map.class);
                        if ( attrs==null) {
                            attrs = new HashMap<>();
                        }
                        attrs.put("品牌", _shelvesItem.getBrandName());

                        // load 参数属性

                        List<VCItemPropValue> propValues = itemAttrService.loadPropValues(_shelvesItem.getCode());
                        List<VCItemExtPropValue> extPropValues = itemAttrService.loadExtPropValues(_shelvesItem.getCode());

                        // 解析参数, 填写属性
                        int cellCnt =  nameHiddenRow.getLastCellNum();
                        for (int i = 5; i < cellCnt; i++) {
                            XSSFCell cell = nameHiddenRow.getCell(i);
                            if ( cell!=null && StringUtils.isNotBlank(getValue(cell)) ) {
                                XSSFCell atrCell = dataRow.getCell(i);
                                if ( atrCell == null ) {
                                    atrCell = dataRow.createCell(i);
                                }

                                if ( StringUtils.isNotBlank(getValue(atrCell)) ) {
                                    continue;
                                }

                                String propid = getValue(codeHiddenRow.getCell(i));
                                if ( "1".equalsIgnoreCase(propid) ) {
                                    atrCell.setCellValue(_shelvesItem.getDim1Val());
                                    continue;
                                } else if ( "2".equalsIgnoreCase(propid) ) {
                                    atrCell.setCellValue(_shelvesItem.getDim2Val());
                                    continue;
                                }

                                atrCell.setCellValue(itemAttrService.getAttrValue(getValue(cell), attrs, propValues, extPropValues));
                            }
                        }

                        if ( attrs!=null && attrs.size()>0) {
                            int index = column+1;
                            for (Map.Entry<String, String> _entry : attrs.entrySet()) {
                                dataRow.createCell(index++).setCellValue(_entry.getKey() + ": " + _entry.getValue());
                            }
                        }
                    }

                    for (int i = 0; i < column + 10; i++) {
                        sheet.autoSizeColumn(i);
                        sheet.setColumnWidth(i, sheet.getColumnWidth(i)*13/10);
                    }


                }
            }


            wb.write(ouputStream);
        } catch (Exception e) {
            LOG.error(e.getMessage(), e.getCause());
        } finally {
            try {
                wb.close();
            } catch (Exception e) {

            }
        }

    }

    @Override
    public Pagination loadItems(int start, int length, ItemCondition condition) {
        Pagination pagination = new Pagination();

        List<Object> params = new ArrayList<>();

        StringBuilder whereCond = new StringBuilder(" where 1 =1 ");

        if ( StringUtils.isNotBlank(condition.getTaskname()) ) {
            whereCond.append("  and task.NAME like ? ");
            params.add("%" + condition.getTaskname().trim() + "%");
        }

        if (StringUtils.isNotBlank(condition.getSkuname()) ) {
            whereCond.append("  and item.SKUNAME like ? ");
            params.add("%" + condition.getSkuname().trim() + "%");
        }

        if (StringUtils.isNotBlank(condition.getCatename()) ) {
            whereCond.append("  and item.CIDNAME1 like ? ");
            params.add("%" + condition.getCatename().trim() + "%");
        }

        if (StringUtils.isNotBlank(condition.getBrandname()) ) {
            whereCond.append("  and item.BRANDNAME like ? ");
            params.add("%" + condition.getBrandname().trim() + "%");
        }

        if (StringUtils.isNotBlank(condition.getZt()) ) {
            whereCond.append("  and item.ZT = ? ");
            params.add(condition.getZt().trim());
        }

        try {

            String sql = "select task.NAME, task.STATE, item.*\n" +
                         "       from vc_shelves_item item\n" +
                         "            left join vc_shelves_task task on task.ID = item.TASKID";

            String cnt_sql = "select count(1) \n" +
                    "       from vc_shelves_item item\n" +
                    "            left join vc_shelves_task task on task.ID = item.TASKID";

            int count = jdbcTemplate.queryForObject(cnt_sql + whereCond.toString(), params.toArray(new Object[params.size()]), Integer.class);

            params.add(start);
            params.add(length);
            List<VCShelvesItem> list = jdbcTemplate.query(sql + whereCond.toString() + " limit ?, ?", params.toArray(new Object[params.size()]), new BeanPropertyRowMapper(VCShelvesItem.class));


            pagination.setRecordsTotal(count);
            pagination.setRecordsFiltered(count);
            pagination.setData(list);
        } catch (Exception e) {
            LOG.error(e.getMessage());
        }

        return pagination;
    }

    // 上架一批SKU
    @Override
    public void shelves(List<VCShelvesItem> items) throws Exception {

        if ( items==null || items.size()==0 ) {
            return;
        }


        Map<String, List<VCShelvesItem>> cidItemMap = new ConcurrentHashMap<>();
        for (VCShelvesItem item : items) {
            String cid = item.getCid1();
            if ( StringUtils.isBlank(cid) ) {
                LOG.warn("[CODE: {}]未设置类目", item.getCode());
                continue;
            }

            if ( !cidItemMap.containsKey(cid) ) {
                cidItemMap.put(cid, new ArrayList<>());
            }
            cidItemMap.get(cid).add(item);
        }

        if ( cidItemMap.size()==0 ) {
            return;
        }

        for (String cid : cidItemMap.keySet()) {
            VCFeature vcFeature = vcCateFutureDao.load(cid);
            boolean isGaea = (vcFeature==null ? false : "true".equalsIgnoreCase(vcFeature.getGaea()));
            LOG.info("[Cid: {}]Is Gaea: {}", cid, isGaea);
            if ( isGaea ) {
                doGaeaShelves(cid, cidItemMap.get(cid));
            } else {
                doShelves(cid, cidItemMap.get(cid));
            }
        }
    }

    /**
     * 非盖亚商品上架
     * @param cid
     * @param items
     */
    private void doShelves(String cid, List<VCShelvesItem> items) throws Exception {

        if ( StringUtils.isBlank(cid) ) {
            LOG.warn("cid is blank.");
            return;
        }

        if ( items==null || items.size()==0 ) {
            LOG.warn("Items is empty.");
            return;
        }

        for (VCShelvesItem item : items) {

            VCPurchaseSale purchaseSale = null;
            try {
                purchaseSale = jdbcTemplate.queryForObject("select * from VC_PURCHASE_SALE where CATE3 = ?", new BeanPropertyRowMapper<>(VCPurchaseSale.class), item.getCidName1());
                if ( StringUtils.isBlank(purchaseSale.getPurchasercode()) ) {
                    throw new RuntimeException("[CODE: " + item.getSkucode()  + "]商品采购员未设置");
                }
                if ( StringUtils.isBlank(purchaseSale.getPurchasercode()) ) {
                    throw new RuntimeException("[CODE: " + item.getSkucode()  + "]商品销售员未设置");
                }
            } catch (Exception e){
                LOG.error(e.getMessage(), e.getCause());
                continue;
            }


            VcItemNewProductCreateRequest request=new VcItemNewProductCreateRequest();
            request.setApplyId(item.getApplyid());

            request.setName(item.getJdskuname());

            if ( StringUtils.isBlank(item.getCid1()) ) {
                throw new RuntimeException("[CODE: " + item.getSkucode()  + "]上架类目未设置");
            }
            request.setCid1(Integer.parseInt(item.getCid1()));

            if ( StringUtils.isBlank(item.getBrandid()) ) {
                throw new RuntimeException("[CODE: " + item.getSkucode()  + "]上架品牌未设置");
            }
            request.setBrandId(Integer.parseInt(item.getBrandid()));

            request.setModel(item.getModel());

            request.setOriginalPlace(item.getOriginalPlace());
            request.setWarranty(item.getWarranty());

//       保质期
//        if ( product.getShelflife()!=null ) {
//            request.setShelfLife(Integer.parseInt(product.getShelflife()));
//        }

            request.setPurchaserCode(purchaseSale.getPurchasercode());
            request.setSalerCode(purchaseSale.getSalercode());


//      TODO 包装规格
//        if ( product.getPacking()==null ) {
//            request.setPacking(1); // 包装规格（[1×N]请填写N的值）
//        } else {
//            request.setPacking(Integer.parseInt(product.getPacking())); // 包装规格（[1×N]请填写N的值）
//        }
            request.setPacking(1); // 包装规格（[1×N]请填写N的值）

            request.setPackType(1); // 1	特殊属性（1:普通商品，2:易碎品，3:裸装液体，4:带包装液体，5:按原包装出库）

            request.setSkuUnit(item.getUnit()); // 销售单位（必须为VC接口提供的销售单位）
            request.setPkgInfo("主体 × 1、说明书 × 1"); // 包装清单（格式：USB线 × 1、AV线 × 1、配带 × 1、说明书 × 1、保修卡 × 1 ，最多可录入20000个字符）



            CategoryInfo categoryInfo = vcCategoryService.loadCategoryInfo(item.getCid1());

            Map attrs = redisTemplate.opsForHash().entries(item.getId() + "_attr");
            /**
             * 属性值操作
             */
            List<VCCategoryProp> props = categoryInfo.getProps();
            if ( props!=null && props.size()>0 ) {

                List<String> ids = new ArrayList<>();
                List<String> vids = new ArrayList<>();
                List<String> values = new ArrayList<>();
                List<String> remark = new ArrayList<>();

                for (VCCategoryProp prop : props) {

                    System.out.println(prop);

                    String propid = prop.getPropid();
                    Object propValue = null;

                    if (attrs.containsKey(propid)) {
                        propValue = attrs.get(propid);
                        System.out.println(prop.getPropname() + " ==> " + propValue);
                    } else {
                        System.out.println(prop.getPropname() + " ==>  属性未设置");
                    }

                    if (propValue == null && !"true".equalsIgnoreCase(prop.getRequired())) {
                        continue;
                    }

                    ids.add(prop.getPropid());

                    if ( "1".equalsIgnoreCase(prop.getInputtype()) ) {
                        vids.add(itemAttrService.getPropVid(prop.getPropid(), propValue));
                        values.add("");
                        remark.add("");
                    } else if ( "2".equalsIgnoreCase(prop.getInputtype()) ) {
                        vids.add("");
                        values.add(itemAttrService.getPropVid(prop.getPropid(), propValue));
                        remark.add("");
                    } else {
                        vids.add("");
                        values.add("");
                        remark.add(propValue.toString());
                    }

                }

                request.setPropId(String.join(",", ids));
                request.setPropVid(String.join(",", vids));
                request.setPropValues(String.join(",", values));
                request.setPropRemark(String.join(",", remark));

            }


            // 扩展属性
            List<VCCategoryExtProp> extProps = categoryInfo.getExtProps();
            if ( extProps!=null && extProps.size()>0 ) {

                List<String> ids = new ArrayList<>();
                List<String> values = new ArrayList<>();
                List<String> remark = new ArrayList<>();

                for (VCCategoryExtProp extProp : extProps) {

                    System.out.println(extProp);

                    String propid = extProp.getAttId();
                    Object propValue = null;

                    if (attrs.containsKey(propid)) {
                        propValue = attrs.get(propid);
                        System.out.println(extProp.getName() + " ==> " + propValue);
                    } else {
                        System.out.println(extProp.getName() + " ==>  属性未设置");
                    }

                    if (propValue == null && !"1".equalsIgnoreCase(extProp.getIsRequired())) {
                        continue;
                    }

                    ids.add(extProp.getAttId());

                    if ( "1".equalsIgnoreCase(extProp.getInputType())
                                ||  "2".equalsIgnoreCase(extProp.getInputType()) ) {
                        values.add(itemAttrService.getExtPropValue(extProp.getAttId(), propValue));
                        remark.add("");
                    } else {
                        values.add("");
                        remark.add(propValue.toString());
                    }

                }

                request.setExtId(String.join(",", ids));
                request.setExtValues(String.join(",", values));
                request.setExtRemark(String.join(",", remark));
            }


            // TODO  商品介绍
            request.setIntroHtml(jdIntroService.buildPCIntroHtml(Lists.newArrayList(item)));            // 	PC端代码录入的商品介绍
            request.setIntroMobile(jdIntroService.buildMobileIntroHtml(Lists.newArrayList(item)));      // 	Mobile端代码录入的商品介绍


            request.setLength(Integer.parseInt(item.getLength()));
            request.setWidth(Integer.parseInt(item.getWidth()));
            request.setHeight(Integer.parseInt(item.getHeight()));
            request.setWeight(new BigDecimal(item.getWeight()));

            request.setMarketPrice(new BigDecimal(item.getMarketPrice()));
            request.setPurchasePrice(new BigDecimal(item.getPurchasePrice()));
            request.setMemberPrice(new BigDecimal(item.getMemberPrice()));

            request.setItemNum(item.getItemNum());

            try {
                LOG.info("[Request]{}", request.getAppJsonParams());
            }catch (Exception e) {
                LOG.error(e.getMessage(), e.getCause());
            }


            JdClient client = JdClientFactory.jdClient();
            VcItemNewProductCreateResponse response=  client.execute(request);
            LOG.info("[Response]{}", new Gson().toJson(response));

            item.setZt(ItemState.FAILED.code());
            if ( response!=null && response.getJosResultDto()!=null ) {
                item.setMessage(response.getJosResultDto().getReturnMessage());

                if ( response.getJosResultDto().getIsSuccess() ) {
                    String result = response.getJosResultDto().getSingleObj();
                    Map<String, String> map =  GSON.fromJson(result, new TypeToken<Map<String, String>>() {}.getType());
                    item.setZt(ItemState.SUCCESS.code());
                    item.setApplyid(map.get("productApplyId"));

                    VCProductApply apply = vcProductApplyDao.load(map.get("productApplyId"));
                    if ( apply!=null ) {
                        apply.setState(ApplyState.STATE_302.code());
                        apply.setReject(false);
                        apply.setOpinion("");
                        apply.setPriceexport("N");
                        apply.setReset("N");
                        apply.setName(request.getName());
                        vcProductApplyDao.update(apply);
                    }
                }
            }
        }
    }

    /**
     *  盖亚商品上架
     *
     * @param cid
     * @param items
     */
    private void doGaeaShelves(String cid, List<VCShelvesItem> items) throws Exception {

        if ( StringUtils.isBlank(cid) ) {
            LOG.warn("cid is blank.");
            return;
        }

        if ( items==null || items.size()==0 ) {
            LOG.warn("Items is empty.");
            return;
        }


        VCShelvesItem product = items.get(0);

        VCPurchaseSale purchaseSale = null;
        try {
            purchaseSale = jdbcTemplate.queryForObject("select * from VC_PURCHASE_SALE where CATE3 = ?", new BeanPropertyRowMapper<>(VCPurchaseSale.class), product.getCidName1());
            if ( StringUtils.isBlank(purchaseSale.getPurchasercode()) ) {
                throw new RuntimeException("[Cate: " + product.getCidName1()  + "]商品采购员未设置");
            }
            if ( StringUtils.isBlank(purchaseSale.getPurchasercode()) ) {
                throw new RuntimeException("[Cate: " + product.getCidName1()  + "]商品销售员未设置");
            }
        } catch (Exception e){
            LOG.error(e.getMessage(), e.getCause());
            return;
        }


        VcItemNewProductCreateRequest request=new VcItemNewProductCreateRequest();
        request.setApplyId(product.getApplyid());

        request.setName(product.getJdproductname());
        if ( StringUtils.isBlank(product.getCid1()) ) {
            throw new RuntimeException("[PRODUCTCODE: " + product.getProductcode()  + "]上架类目未设置");
        }
        request.setCid1(Integer.parseInt(product.getCid1()));

        if ( StringUtils.isBlank(product.getBrandid()) ) {
            throw new RuntimeException("[PRODUCTCODE: " + product.getProductcode()  + "]上架品牌未设置");
        }
        request.setBrandId(Integer.parseInt(product.getBrandid()));

        request.setModel(product.getModel());

        request.setOriginalPlace(product.getOriginalPlace());
        request.setWarranty(product.getWarranty());

//       保质期
//        if ( product.getShelflife()!=null ) {
//            request.setShelfLife(Integer.parseInt(product.getShelflife()));
//        }

        request.setPurchaserCode(purchaseSale.getPurchasercode());
        request.setSalerCode(purchaseSale.getSalercode());

        request.setPacking(1); // 包装规格（[1×N]请填写N的值）

        request.setPackType(1); // 1	特殊属性（1:普通商品，2:易碎品，3:裸装液体，4:带包装液体，5:按原包装出库）

        request.setSkuUnit(product.getUnit()); // 销售单位（必须为VC接口提供的销售单位）
        request.setPkgInfo("主体 × 1、说明书 × 1"); // 包装清单（格式：USB线 × 1、AV线 × 1、配带 × 1、说明书 × 1、保修卡 × 1 ，最多可录入20000个字符）

        /**
         * 属性值操作
         */

        CategoryInfo categoryInfo = vcCategoryService.loadCategoryInfo(product.getCid1());

        Map attrs = redisTemplate.opsForHash().entries(product.getId() + "_attr");
        /**
         * 属性值操作
         */
        List<VCCategoryProp> props = categoryInfo.getProps();
        if ( props!=null && props.size()>0 ) {

            List<String> ids = new ArrayList<>();
            List<String> vids = new ArrayList<>();
            List<String> values = new ArrayList<>();
            List<String> remark = new ArrayList<>();

            for (VCCategoryProp prop : props) {

                System.out.println(prop);

                String propid = prop.getPropid();
                Object propValue = null;

                if (attrs.containsKey(propid)) {
                    propValue = attrs.get(propid);
                    System.out.println(prop.getPropname() + " ==> " + propValue);
                } else {
                    System.out.println(prop.getPropname() + " ==>  属性未设置");
                }

                if (propValue == null && !"true".equalsIgnoreCase(prop.getRequired())) {
                    continue;
                }

                ids.add(prop.getPropid());

                if ( "1".equalsIgnoreCase(prop.getInputtype()) ) {
                    vids.add(itemAttrService.getPropVid(prop.getPropid(), propValue));
                    values.add("");
                    remark.add("");
                } else if ( "2".equalsIgnoreCase(prop.getInputtype()) ) {
                    vids.add("");
                    values.add(itemAttrService.getPropVid(prop.getPropid(), propValue));
                    remark.add("");
                } else {
                    vids.add("");
                    values.add("");
                    remark.add(propValue.toString());
                }

            }

            request.setPropId(String.join(",", ids));
            request.setPropVid(String.join(",", vids));
            request.setPropValues(String.join(",", values));
            request.setPropRemark(String.join(",", remark));

        }


        // 扩展属性
        List<VCCategoryExtProp> extProps = categoryInfo.getExtProps();
        if ( extProps!=null && extProps.size()>0 ) {

            List<String> ids = new ArrayList<>();
            List<String> values = new ArrayList<>();
            List<String> remark = new ArrayList<>();

            for (VCCategoryExtProp extProp : extProps) {

                System.out.println(extProp);

                String propid = extProp.getAttId();
                Object propValue = null;

                if (attrs.containsKey(propid)) {
                    propValue = attrs.get(propid);
                    System.out.println(extProp.getName() + " ==> " + propValue);
                } else {
                    System.out.println(extProp.getName() + " ==>  属性未设置");
                }

                if (propValue == null && !"1".equalsIgnoreCase(extProp.getIsRequired())) {
                    continue;
                }

                ids.add(extProp.getAttId());

                if ( "1".equalsIgnoreCase(extProp.getInputType())
                        ||  "2".equalsIgnoreCase(extProp.getInputType()) ) {
                    values.add(itemAttrService.getExtPropValue(extProp.getAttId(), propValue));
                    remark.add("");
                } else {
                    values.add("");
                    remark.add(propValue.toString());
                }

            }

            request.setExtId(String.join(",", ids));
            request.setExtValues(String.join(",", values));
            request.setExtRemark(String.join(",", remark));
        }



        request.setIntroHtml(jdIntroService.buildPCIntroHtml(items));    // 	PC端代码录入的商品介绍
        request.setIntroMobile(jdIntroService.buildMobileIntroHtml(items));    // 	Mobile端代码录入的商品介绍

        StringBuilder weight = new StringBuilder();
        StringBuilder length = new StringBuilder();
        StringBuilder width = new StringBuilder();
        StringBuilder height = new StringBuilder();

        StringBuilder marketPrice = new StringBuilder();
        StringBuilder purchasePrice = new StringBuilder();
        StringBuilder memberPrice = new StringBuilder();

        StringBuilder skuNameGaea = new StringBuilder();

        StringBuilder itemNum = new StringBuilder();

        StringBuilder dim1SortGaea = new StringBuilder();
        StringBuilder dim1ValGaea = new StringBuilder();

        StringBuilder dim2SortGaea = new StringBuilder();
        StringBuilder dim2ValGaea = new StringBuilder();

        for (VCShelvesItem shelvesItem : items) {
            weight.append(shelvesItem.getWeight()).append(",");

            length.append(Integer.parseInt(shelvesItem.getLength())).append(",");
            width.append(Integer.parseInt(shelvesItem.getWidth())).append(",");
            height.append(Integer.parseInt(shelvesItem.getHeight())).append(",");

            marketPrice.append(shelvesItem.getMarketPrice()).append(",");
            purchasePrice.append(shelvesItem.getPurchasePrice()).append(",");
            memberPrice.append(shelvesItem.getMemberPrice()).append(",");

            skuNameGaea.append(shelvesItem.getJdskuname()).append(",");
            itemNum.append(shelvesItem.getItemNum()).append(",");

            dim1SortGaea.append(shelvesItem.getDim1Sort()).append(",");
            dim1ValGaea.append(shelvesItem.getDim1Val()).append(",");

            dim2SortGaea.append(shelvesItem.getDim2Sort()).append(",");
            dim2ValGaea.append(shelvesItem.getDim2Val()).append(",");
        }


        request.setWeightGaea(weight.substring(0, weight.length()-1));
        request.setLengthGaea(length.substring(0, length.length()-1));
        request.setWidthGaea(width.substring(0, width.length()-1));
        request.setHeightGaea(height.substring(0, height.length()-1));

        request.setMarketPriceGaea(marketPrice.substring(0, marketPrice.length()-1));
        request.setPurchasePriceGaea(purchasePrice.substring(0, purchasePrice.length()-1));
        request.setMemberPriceGaea(memberPrice.substring(0, memberPrice.length()-1));

        request.setSkuNameGaea(skuNameGaea.substring(0, skuNameGaea.length()-1));
        request.setItemNumGaea(itemNum.substring(0, itemNum.length()-1));

        request.setDim1SortGaea(dim1SortGaea.substring(0, dim1SortGaea.length()-1));
        request.setDim1ValGaea(dim1ValGaea.substring(0, dim1ValGaea.length()-1));

        request.setDim2SortGaea(dim2SortGaea.substring(0, dim2SortGaea.length()-1));
        request.setDim2ValGaea(dim2ValGaea.substring(0, dim2ValGaea.length()-1));

        try {
            LOG.info("[Request]{}", request.getAppJsonParams());
        }catch (Exception e) {
            LOG.error(e.getMessage(), e.getCause());
        }

        JdClient client = JdClientFactory.jdClient();
        VcItemNewProductCreateResponse response =  client.execute(request);
        LOG.info("[Response]{}", new Gson().toJson(response));


        if (  response!=null && response.getJosResultDto()!=null ) {
            for (VCShelvesItem item : items) {
                item.setZt(ItemState.FAILED.code());
                item.setMessage(response.getJosResultDto().getReturnMessage());
            }
        }

        if ( response!=null && response.getJosResultDto()!=null && response.getJosResultDto().getIsSuccess() ) {
            String result = response.getJosResultDto().getSingleObj();
            Map<String, String> map =  GSON.fromJson(result, new TypeToken<Map<String, String>>() {}.getType());
            for (VCShelvesItem item : items) {
                item.setZt(ItemState.SUCCESS.code());
                item.setApplyid(map.get("productApplyId"));
            }

            VCProductApply apply = vcProductApplyDao.load(map.get("productApplyId"));
            if ( apply!=null ) {
                apply.setState(ApplyState.STATE_302.code());
                apply.setReject(false);
                apply.setOpinion("");
                apply.setPriceexport("N");
                apply.setReset("N");
                apply.setName(request.getName());
                vcProductApplyDao.update(apply);
            }
        }
    }

    private String getValue(XSSFCell cell) {
        return getValue(cell, "");
    }

    private String getValue(XSSFCell cell, String defaultValue) {
        if ( cell==null ) {
            return defaultValue;
        }

        String value = "";

        switch ( cell.getCellTypeEnum() ){
            case _NONE:
            case BLANK:
            case ERROR:
            case STRING:
                value =  cell.getStringCellValue();
                break;
            case BOOLEAN:
                value = String.valueOf(cell.getBooleanCellValue());
                break;
            case FORMULA:
                break;
            case NUMERIC:
                value = new BigDecimal(cell.getNumericCellValue()).setScale(0, RoundingMode.HALF_DOWN).toPlainString();
                break;
            default:
        }

        return StringUtils.isBlank(value) ? defaultValue : value;
    }




    private void shelves(VCProduct product) throws Exception {

        if ( product==null ) {
           return;
        }

        List<VCItem> vcItems = SQLExecutor.executeQueryBeanList("select * from VC_ITEM where PRODUCTCODE = ?", VCItem.class, product.getOriginalCode());
        if ( vcItems==null || vcItems.size()==0 ) {
            throw new RuntimeException("[PRODUCTCODE: " + product.getOriginalCode()  + "]商品Item为空");
        }

        System.out.println("开始上架处理: " + product.getName());

        for (VCItem vcItem : vcItems) {
            System.out.println(vcItem.getSkuName());
        }

        VcItemNewProductCreateRequest request=new VcItemNewProductCreateRequest();
        request.setName(product.getName());

        if ( product.getCid1()==null ) {
            throw new RuntimeException("[PRODUCTCODE: " + product.getOriginalCode()  + "]上架类目未设置");
        }
        request.setCid1(Integer.parseInt(product.getCid1()));

        if ( product.getBrandid()==null ) {
            throw new RuntimeException("[PRODUCTCODE: " + product.getOriginalCode()  + "]上架品牌未设置");
        }
        request.setBrandId(Integer.parseInt(product.getBrandid()));

        request.setModel(product.getModel());
        request.setOriginalPlace(product.getOriginalplace());
        request.setWarranty(product.getWarranty());

        if ( product.getShelflife()!=null ) {
            request.setShelfLife(Integer.parseInt(product.getShelflife()));
        }

        if ( product.getSalercode()==null ) {
            throw new RuntimeException("[PRODUCTCODE: " + product.getOriginalCode()  + "]商品销售员未设置");
        }
        request.setSalerCode(product.getSalercode());

        if ( product.getPurchasercode()==null ) {
            throw new RuntimeException("[PRODUCTCODE: " + product.getOriginalCode()  + "]商品采购员未设置");
        }
        request.setPurchaserCode(product.getPurchasercode());

        if ( product.getPacking()==null ) {
            request.setPacking(1); // 包装规格（[1×N]请填写N的值）
        } else {
            request.setPacking(Integer.parseInt(product.getPacking())); // 包装规格（[1×N]请填写N的值）
        }

        if ( product.getPacktype()==null ) {
            request.setPackType(1); //  1	特殊属性（1:普通商品，2:易碎品，3:裸装液体，4:带包装液体，5:按原包装出库）
        } else {
            request.setPackType(Integer.parseInt(product.getPacktype())); // 1	特殊属性（1:普通商品，2:易碎品，3:裸装液体，4:带包装液体，5:按原包装出库）
        }

        request.setSkuUnit(product.getSkuunit()); // 销售单位（必须为VC接口提供的销售单位）
        request.setPkgInfo(product.getPkginfo()); // 包装清单（格式：USB线 × 1、AV线 × 1、配带 × 1、说明书 × 1、保修卡 × 1 ，最多可录入20000个字符）


        /**
         * 属性值操作
         */
        List<VCItemPropValue> props = itemPropService.loadPropValues(product, null);
        if ( props!=null && props.size()>0 ) {
            StringBuilder ids = new StringBuilder();
            StringBuilder vids = new StringBuilder();
            StringBuilder values = new StringBuilder();
            StringBuilder remark = new StringBuilder();

            boolean hasVids = false;
            boolean hasValues = false;
            boolean hasRemarks = false;

            for (VCItemPropValue prop : props) {

                if ( "false".equalsIgnoreCase(prop.getRequired())
                        && StringUtils.isBlank(prop.getPropvid())
                        && StringUtils.isBlank(prop.getPropvalues())
                        && StringUtils.isBlank(prop.getPropremark())  ) {
                    continue;
                }

                ids.append(prop.getPropid()).append(",");
                System.out.println(prop.getPropid() + ": " + prop.getPropname() + ": " + prop.getType());

                if ( "1".equalsIgnoreCase(prop.getType()) ) {
                    vids.append(prop.getPropvid()).append(",");
                    values.append(",");
                    remark.append(",");
                    hasVids = true;
                } else if ( "2".equalsIgnoreCase(prop.getType()) ) {
                    String _values = prop.getPropvalues();
                    if ( StringUtils.isBlank(_values) ) {
                        _values = "";
                    }
                    vids.append(",");
                    values.append(_values.replace(",", ";")).append(",");
                    remark.append(",");
                    hasValues = true;
                } else {
                    vids.append(",");
                    values.append(",");
                    remark.append(prop.getPropremark()).append(",");
                    hasRemarks =true;
                }

            }

            request.setPropId(ids.substring(0, ids.length()-1));
            if ( hasVids ) {
                request.setPropVid(vids.substring(0, vids.length()-1));
            }
            if ( hasValues ) {
                request.setPropValues(values.substring(0, values.length()-1));
            }
            if ( hasRemarks ) {
                request.setPropRemark(remark.substring(0, remark.length()-1));
            }
        }


        List<VCItemExtPropValue> extProps = itemPropService.loadExtPropValues(product,null);
        if ( extProps!=null && extProps.size()>0 ) {
            System.out.println("扩展属性: ");

            StringBuilder ids = new StringBuilder();
            StringBuilder values = new StringBuilder();
            StringBuilder remark = new StringBuilder();

            boolean hasValues = false;
            boolean hasRemark = false;

            for (VCItemExtPropValue prop : extProps) {
                System.out.println(prop.getPropid() + ": " + prop.getPropname() + ": " + prop.getType());
                ids.append(prop.getPropid()).append(",");
                if ( "1".equalsIgnoreCase(prop.getType()) || "2".equalsIgnoreCase(prop.getType()) ) {
                    values.append(prop.getExtvalue()).append(",");
                    remark.append(",");
                    hasValues = true;
                } else {
                    hasRemark = true;
                    values.append(",");
                    remark.append(prop.getExtremark()).append(",");
                }
            }

            request.setExtId(ids.substring(0, ids.length()-1));
            if ( hasValues ) {
                request.setExtValues(values.substring(0, values.length()-1));
            }

            if ( hasRemark ) {
                request.setExtRemark(remark.substring(0, remark.length()-1));
            }
        }

////        request.setSkuNameGaea(request.getName().replace("，", " "));
////
//        String picurl = "";
//        List<VCProductImage> images = SQLExecutor.executeQueryBeanList("select * from VC_PRODUCT_IMAGE where CODE = ? and TYPE = 'PRODUCT' ", VCProductImage.class, product.getOriginalCode());
//        if ( images!=null && images.size()>0 ) {
//            for (VCProductImage image : images) {
//                System.out.println(image.getPos() + ". " + image.getUrl());
//                System.out.println("\t" + image.getUrl().replace("https://static.mymro.cn/", "http://119.45.202.163:9080/images/"));
//
//                picurl = ImageLoader.load(image.getUrl());
//
//                if (StringUtils.isNotBlank(picurl)) {
//                    break;
//                }
//            }
//        } else {
//            System.err.println("商品图片为空！");
//        }
////
//
//        String intro = buileIntro(product.getIntro(),  picurl);


        request.setIntroHtml(product.getIntrohtml());
        request.setIntroMobile(product.getIntromobile());

        JdClient client = JdClientFactory.jdClient();


        VCFeature vcFeature = vcCateFutureDao.load(product.getCid1());

        boolean isGaea = (vcFeature==null ? false : "true".equalsIgnoreCase(vcFeature.getGaea()));
        System.out.println("Is Gaea: " + isGaea);

        if ( !isGaea ) {
            VCItem item = vcItems.get(0);

            request.setWeight(new BigDecimal(item.getWeight()));
            request.setLength(Integer.parseInt(item.getLength()));
            request.setWidth(Integer.parseInt(item.getWidth()));
            request.setHeight(Integer.parseInt(item.getHeight()));

            request.setMarketPrice(new BigDecimal(item.getMarketPrice()));
            request.setPurchasePrice(new BigDecimal(item.getPurchasePrice()));
            request.setMemberPrice(new BigDecimal(item.getMemberPrice()));

//            request.setSkuNameGaea(request.getName().replace("，", " "));
            request.setItemNum(item.getItemNum());

//            request.setDim1SortGaea(item.getDim1Sort());
//            request.setDim1ValGaea(item.getDim1Val());
//
//            request.setDim2SortGaea(item.getDim2Sort());
//            request.setDim2ValGaea(item.getDim2Val());
        }

        if ( (vcItems.size() > 1) || isGaea ) {

            StringBuilder weight = new StringBuilder();
            StringBuilder length = new StringBuilder();
            StringBuilder width = new StringBuilder();
            StringBuilder height = new StringBuilder();

            StringBuilder marketPrice = new StringBuilder();
            StringBuilder purchasePrice = new StringBuilder();
            StringBuilder memberPrice = new StringBuilder();

            StringBuilder skuNameGaea = new StringBuilder();

            StringBuilder itemNum = new StringBuilder();

            StringBuilder dim1SortGaea = new StringBuilder();
            StringBuilder dim1ValGaea = new StringBuilder();

            StringBuilder dim2SortGaea = new StringBuilder();
            StringBuilder dim2ValGaea = new StringBuilder();

            for (VCItem vcItem : vcItems) {

                weight.append(vcItem.getWeight()).append(",");

                length.append(Integer.parseInt(vcItem.getLength())).append(",");
                width.append(Integer.parseInt(vcItem.getWidth())).append(",");
                height.append(Integer.parseInt(vcItem.getHeight())).append(",");

                marketPrice.append(vcItem.getMarketPrice()).append(",");
                purchasePrice.append(vcItem.getPurchasePrice()).append(",");
                memberPrice.append(vcItem.getMemberPrice()).append(",");

                skuNameGaea.append(vcItem.getSkuName().replace("，", " ")).append(",");
                itemNum.append(vcItem.getItemNum()).append(",");

                dim1SortGaea.append(vcItem.getDim1Sort()).append(",");
                dim1ValGaea.append(vcItem.getDim1Val()).append(",");

                dim2SortGaea.append(vcItem.getDim2Sort()).append(",");
                dim2ValGaea.append(vcItem.getDim2Val()).append(",");

            }


            request.setWeightGaea(weight.substring(0, weight.length()-1));
            request.setLengthGaea(length.substring(0, length.length()-1));
            request.setWidthGaea(width.substring(0, width.length()-1));
            request.setHeightGaea(height.substring(0, height.length()-1));

            request.setMarketPriceGaea(marketPrice.substring(0, marketPrice.length()-1));
            request.setPurchasePriceGaea(purchasePrice.substring(0, purchasePrice.length()-1));
            request.setMemberPriceGaea(memberPrice.substring(0, memberPrice.length()-1));

            request.setSkuNameGaea(skuNameGaea.substring(0, skuNameGaea.length()-1));
            request.setItemNumGaea(itemNum.substring(0, itemNum.length()-1));

            request.setDim1SortGaea(dim1SortGaea.substring(0, dim1SortGaea.length()-1));
            request.setDim1ValGaea(dim1ValGaea.substring(0, dim1ValGaea.length()-1));

            request.setDim2SortGaea(dim2SortGaea.substring(0, dim2SortGaea.length()-1));
            request.setDim2ValGaea(dim2ValGaea.substring(0, dim2ValGaea.length()-1));


        }

        System.out.println(request.getAppJsonParams());

        VcItemNewProductCreateResponse response=  client.execute(request);
        System.out.println(new Gson().toJson(response));

        if ( response!=null && response.getJosResultDto()!=null && response.getJosResultDto().getIsSuccess() ) {
            String result = response.getJosResultDto().getSingleObj();

            Map<String, String> map =  GSON.fromJson(result, new TypeToken<Map<String, String>>() {}.getType());
           //   GSON.
           vcProductDao.updateApplyId(product.getId(), map.get("productApplyId"));

        }

        if ( response.getJosResultDto()!=null && !response.getJosResultDto().getIsSuccess() ) {
            throw new RuntimeException(response.getJosResultDto().getReturnMessage());
        }

//         if ( response.getJosResultDto().getIsSuccess() ) {
//             product.set(response.getJosResultDto().getSingleObj());
//         }

    }

    @Override
    public void updateShelves(VCProduct product) throws Exception {

        if ( product==null ) {
            return;
        }

        List<VCItem> vcItems = SQLExecutor.executeQueryBeanList("select * from VC_ITEM where WAREID = ?", VCItem.class, product.getWareId());
        if ( vcItems==null || vcItems.size()==0 ) {
            throw  new RuntimeException("[VC_ITEM]商品ITEM记录[" + product.getWareId() + "]为空, 跳过！");
        }

//
//        List<VCShelvesItem> vcShelvesItems = SQLExecutor.executeQueryBeanList("select * from VC_SHELVES_ITEM where SKUCODE = ?", VCShelvesItem.class, CodeEncoder.decoder(vcItems.get(0).getItemNum()));
//        if ( vcShelvesItems==null || vcShelvesItems.size()==0 ) {
//            throw  new RuntimeException("[VC_SHELVES_ITEM]商品ITEM记录[" + CodeEncoder.decoder(vcItems.get(0).getItemNum()) + "]为空, 跳过！");
//        }


        System.out.println("开始上架处理: " + product.getName());

        for (VCItem vcItem : vcItems) {
            System.out.println(vcItem.getSkuName());
        }

        VcItemOldProductUpdateRequest request = new VcItemOldProductUpdateRequest();
        request.setName(product.getName());
//        request.setApplyId(vcShelvesItems.get(0).getApplyid());
        request.setWareId(product.getWareId());

        if ( product.getCid1()==null ) {
            throw new RuntimeException("[PRODUCTCODE: " + product.getOriginalCode()  + "]上架类目未设置");
        }
        request.setCid1(Integer.parseInt(product.getCid1()));

        if ( product.getBrandid()==null ) {
            throw new RuntimeException("[PRODUCTCODE: " + product.getOriginalCode()  + "]上架品牌未设置");
        }
        request.setBrandId(Integer.parseInt(product.getBrandid()));
        request.setZhBrand(product.getZhbrand());
        request.setEnBrand(product.getEnbrand());

        request.setOriginalPlace(product.getOriginalplace());
        request.setWarranty(product.getWarranty());

        if ( product.getSalercode()==null ) {
            throw new RuntimeException("[PRODUCTCODE: " + product.getOriginalCode()  + "]商品销售员未设置");
        }
        request.setSalerCode(product.getSalercode());

        if ( product.getPurchasercode()==null ) {
            throw new RuntimeException("[PRODUCTCODE: " + product.getOriginalCode()  + "]商品采购员未设置");
        }
        request.setPurchaserCode(product.getPurchasercode());

        request.setSkuUnit(product.getSkuunit()); // 销售单位（必须为VC接口提供的销售单位）
        request.setPkgInfo(product.getPkginfo()); // 包装清单（格式：USB线 × 1、AV线 × 1、配带 × 1、说明书 × 1、保修卡 × 1 ，最多可录入20000个字符）
        request.setSkuIdGaea(product.getWareId());

        /**
         * 属性值操作
         */
        List<VCItemPropValue> props = itemPropService.loadPropValues(product, vcItems.get(0));
        if ( props!=null && props.size()>0 ) {
            StringBuilder ids = new StringBuilder();
            StringBuilder vids = new StringBuilder();
            StringBuilder values = new StringBuilder();
            StringBuilder remark = new StringBuilder();

            boolean hasVids = false;
            boolean hasValues = false;
            boolean hasRemarks = false;

            for (VCItemPropValue prop : props) {

                if ( "false".equalsIgnoreCase(prop.getRequired())
                        && StringUtils.isBlank(prop.getPropvid())
                        && StringUtils.isBlank(prop.getPropvalues())
                        && StringUtils.isBlank(prop.getPropremark())  ) {
                    continue;
                }

                ids.append(prop.getPropid()).append(",");
                System.out.println(prop.getPropid() + ": " + prop.getPropname() + ": " + prop.getType());

                if ( "1".equalsIgnoreCase(prop.getType()) ) {
                    vids.append(prop.getPropvid()).append(",");
                    values.append(",");
                    remark.append(",");
                    hasVids = true;
                } else if ( "2".equalsIgnoreCase(prop.getType()) ) {
                    String _values = prop.getPropvalues();
                    if ( StringUtils.isBlank(_values) ) {
                        _values = "";
                    }
                    vids.append(",");
                    values.append(_values.replace(",", ";")).append(",");
                    remark.append(",");
                    hasValues = true;
                } else {
                    vids.append(",");
                    values.append(",");
                    remark.append(prop.getPropremark()).append(",");
                    hasRemarks =true;
                }

            }

            request.setPropId(ids.substring(0, ids.length()-1));
            if ( hasVids ) {
                request.setPropVid(vids.substring(0, vids.length()-1));
            }
            if ( hasValues ) {
                request.setPropValues(values.substring(0, values.length()-1));
            }
            if ( hasRemarks ) {
                request.setPropRemark(remark.substring(0, remark.length()-1));
            }
        }


        List<VCItemExtPropValue> extProps = itemPropService.loadExtPropValues(product, vcItems.get(0));
        if ( extProps!=null && extProps.size()>0 ) {
            System.out.println("扩展属性: ");

            StringBuilder ids = new StringBuilder();
            StringBuilder values = new StringBuilder();
            StringBuilder remark = new StringBuilder();

            boolean hasValues = false;
            boolean hasRemark = false;

            for (VCItemExtPropValue prop : extProps) {
                System.out.println(prop.getPropid() + ": " + prop.getPropname() + ": " + prop.getType());
                ids.append(prop.getPropid()).append(",");
                if ( "1".equalsIgnoreCase(prop.getType()) || "2".equalsIgnoreCase(prop.getType()) ) {
                    values.append(prop.getExtvalue()).append(",");
                    remark.append(",");
                    hasValues = true;
                } else {
                    hasRemark = true;
                    values.append(",");
                    remark.append(prop.getExtremark()).append(",");
                }
            }

            request.setExtId(ids.substring(0, ids.length()-1));
            if ( hasValues ) {
                request.setExtValues(values.substring(0, values.length()-1));
            }

            if ( hasRemark ) {
                request.setExtRemark(remark.substring(0, remark.length()-1));
            }
        }

////        request.setSkuNameGaea(request.getName().replace("，", " "));
////
//        String picurl = "";
//        List<VCProductImage> images = SQLExecutor.executeQueryBeanList("select * from VC_PRODUCT_IMAGE where CODE = ? and TYPE = 'PRODUCT' ", VCProductImage.class, product.getOriginalCode());
//        if ( images!=null && images.size()>0 ) {
//            for (VCProductImage image : images) {
//                System.out.println(image.getPos() + ". " + image.getUrl());
//                System.out.println("\t" + image.getUrl().replace("https://static.mymro.cn/", "http://119.45.202.163:9080/images/"));
//
//                picurl = ImageLoader.load(image.getUrl());
//
//                if (StringUtils.isNotBlank(picurl)) {
//                    break;
//                }
//            }
//        } else {
//            System.err.println("商品图片为空！");
//        }
////
//
//        String intro = buileIntro(product.getIntro(),  picurl);

        request.setIntroHtml(jdIntroService.buildPCIntroHtml2(vcItems));            // 	PC端代码录入的商品介绍
        request.setIntroMobile(jdIntroService.buildMobileIntroHtml2(vcItems));

        JdClient client = JdClientFactory.jdClient();


        VCFeature vcFeature = vcCateFutureDao.load(product.getCid1());

        boolean isGaea = (vcFeature==null ? false : "true".equalsIgnoreCase(vcFeature.getGaea()));
        System.out.println("Is Gaea: " + isGaea);

        if ( !isGaea ) {
            VCItem item = vcItems.get(0);

            request.setWeightGaea(item.getWeight());
            request.setLengthGaea(item.getLength());
            request.setWidthGaea(item.getWidth());
            request.setHeightGaea(item.getHeight());

            request.setMarketPriceGaea(item.getMarketPrice());
            request.setPurchasePriceGaea(item.getPurchasePrice());
            request.setMemberPriceGaea(item.getMemberPrice());

            request.setSkuNameGaea(request.getName().replace("，", " "));
            request.setItemNum(item.getItemNum());

            request.setDim1SortGaea(item.getDim1Sort());
            request.setDim1ValGaea(item.getDim1Val());
//
            request.setDim2SortGaea(item.getDim2Sort());
            request.setDim2ValGaea(item.getDim2Val());
        }

        if ( (vcItems.size() > 1) || isGaea ) {

            StringBuilder weight = new StringBuilder();
            StringBuilder length = new StringBuilder();
            StringBuilder width = new StringBuilder();
            StringBuilder height = new StringBuilder();

            StringBuilder marketPrice = new StringBuilder();
            StringBuilder purchasePrice = new StringBuilder();
            StringBuilder memberPrice = new StringBuilder();

            StringBuilder skuNameGaea = new StringBuilder();
            StringBuilder skuIdGaea = new StringBuilder();

            StringBuilder itemNum = new StringBuilder();

            StringBuilder dim1SortGaea = new StringBuilder();
            StringBuilder dim1ValGaea = new StringBuilder();

            StringBuilder dim2SortGaea = new StringBuilder();
            StringBuilder dim2ValGaea = new StringBuilder();

            for (VCItem vcItem : vcItems) {

                weight.append(vcItem.getWeight()).append(",");

                length.append(Integer.parseInt(vcItem.getLength())).append(",");
                width.append(Integer.parseInt(vcItem.getWidth())).append(",");
                height.append(Integer.parseInt(vcItem.getHeight())).append(",");

                marketPrice.append(vcItem.getMarketPrice()).append(",");
                purchasePrice.append(vcItem.getPurchasePrice()).append(",");
                memberPrice.append(vcItem.getMemberPrice()).append(",");

                skuNameGaea.append(vcItem.getSkuName().replace("，", " ")).append(",");
                itemNum.append(vcItem.getItemNum()).append(",");

                dim1SortGaea.append(vcItem.getDim1Sort()).append(",");
                dim1ValGaea.append(vcItem.getDim1Val()).append(",");

                dim2SortGaea.append(vcItem.getDim2Sort()).append(",");
                dim2ValGaea.append(vcItem.getDim2Val()).append(",");

                skuIdGaea.append(vcItem.getSkuId()).append(",");
            }


            request.setWeightGaea(weight.substring(0, weight.length()-1));
            request.setLengthGaea(length.substring(0, length.length()-1));
            request.setWidthGaea(width.substring(0, width.length()-1));
            request.setHeightGaea(height.substring(0, height.length()-1));

            request.setMarketPriceGaea(marketPrice.substring(0, marketPrice.length()-1));
            request.setPurchasePriceGaea(purchasePrice.substring(0, purchasePrice.length()-1));
            request.setMemberPriceGaea(memberPrice.substring(0, memberPrice.length()-1));

            request.setSkuNameGaea(skuNameGaea.substring(0, skuNameGaea.length()-1));
            request.setItemNumGaea(itemNum.substring(0, itemNum.length()-1));

            request.setDim1SortGaea(dim1SortGaea.substring(0, dim1SortGaea.length()-1));
            request.setDim1ValGaea(dim1ValGaea.substring(0, dim1ValGaea.length()-1));

            request.setDim2SortGaea(dim2SortGaea.substring(0, dim2SortGaea.length()-1));
            request.setDim2ValGaea(dim2ValGaea.substring(0, dim2ValGaea.length()-1));

            request.setSkuIdGaea(skuIdGaea.substring(0, skuIdGaea.length()-1));
        }

        System.out.println(request.getAppJsonParams());

        VcItemOldProductUpdateResponse response=  client.execute(request);
        System.out.println(new Gson().toJson(response));

        if ( response!=null && response.getJosResultDto()!=null && response.getJosResultDto().getIsSuccess() ) {
            JosStringDto result = response.getJosResultDto();

//            Map<String, String> map =  GSON.fromJson(result, new TypeToken<Map<String, String>>() {}.getType());
            //   GSON.
            vcProductDao.updateApplyId(product.getId(), result.getApplyId());

        }

        if ( response.getJosResultDto()!=null && !response.getJosResultDto().getIsSuccess() ) {
            throw new RuntimeException(response.getJosResultDto().getReturnMessage());
        }
//
//         if ( response.getJosResultDto().getIsSuccess() ) {
//             product.set(response.getJosResultDto().getSingleObj());
//         }

    }


    private void pushEvent(Object data){

        if ( sseCache.size()>0 ) {

            for (SseEmitter sseEmitter : sseCache.values()) {
                try {
                    sseEmitter.send( data);
                } catch (IOException e) {
                    e.printStackTrace();
                }
            }

        }


    }

    private void buildProduct(String productTitle,  List<Item> skuItems, String taskid){

        if ( skuItems==null || skuItems.size()==0 ) {
            return;
        }

        MYMROSkuParser parser = new MYMROSkuParser();

        Item spu = null;
        SkuItem spuItem = null;

        for (Item _item : skuItems) {
            HtmlCache htmlCache = htmlCacheDao.load(_item.getUrl());
            if ( htmlCache==null || htmlCache.getHtml()==null ) {
                LOG.warn("[URL: " + _item.getUrl() + "]html不存在!");
                continue;
            }

            try {
                spuItem = parser.parser(_item.getUrl(), htmlCache.getHtml());
                spu = _item;
                break;
            } catch (Exception e) {
                LOG.warn("[URL: " + _item.getUrl() + "]解析失败: " + e.getMessage());
            }
        }

        if ( spuItem==null ) {
            throw new RuntimeException("产品信息解析失败");
        }

        VCProduct vcProduct = new VCProduct();

        vcProduct.setId(IdentifierGen.uuid());
        vcProduct.setTaskid(taskid);
        vcProduct.setOriginalCode(spu.getProductcode());

        vcProduct.setOriginalBrand(spu.getBrandname());
        vcProduct.setOriginalCategory(spu.getCategoryname());
        vcProduct.setOriginalCategoryDesc(spu.getCategorydesc());

        vcProduct.setName(productTitle);

        vcProduct.setCid1(null);// 类目
        vcProduct.setBrandid(null); //

        vcProduct.setModel(spu.getProductname());  // 规格参数中显示商品标题: brand + model

        vcProduct.setOriginalplace("中国大陆"); // 产地
        vcProduct.setWarranty("1个月质保");     // 质保

        vcProduct.setSalercode(SALER_CODE);
        vcProduct.setPurchasercode(PURCHASER_CODE);

        vcProduct.setPacking("1"); // 包装规格（[1×N]请填写N的值）
        vcProduct.setPacktype("1"); // 特殊属性（1:普通商品，2:易碎品，3:裸装液体，4:带包装液体，5:按原包装出库）
        vcProduct.setSkuunit(spuItem.getUnit()); // 箱	销售单位（必须为VC接口提供的销售单位）

        vcProduct.setPkginfo("主产品 × 1"); // USB线 × 1	包装清单（格式：USB线 × 1、AV线 × 1、配带 × 1、说明书 × 1、保修卡 × 1 ，最多可录入20000个字符）
        vcProduct.setDangervalue("0");    // 	danger_value	Number[]	否	1	危险品的值
        vcProduct.setSysp("0");    // 	试用品标识[0:否 1:是]
        vcProduct.setGiftsgoods("0");    // 	赠品标识[0:否 1:是不可销售 2：是可销售]
        vcProduct.setHastransfereleccode("0");    // 	是否需要电子监管码[0:不需要 1:需要]
        // vcProduct.setAftersaledesc("");    // 	售后保障图文详情字符串
        // vcProduct.setWreadme("");    // 	鞋品材料：橡胶	规格参数手工录入文本


        vcProduct.setIntrohtml(introBuilderService.buildPCIntroHtml(spuItem, skuItems));    // 	PC端代码录入的商品介绍
        vcProduct.setIntromobile(introBuilderService.buildMobileIntroHtml(spuItem, skuItems));    // 	Mobile端代码录入的商品介绍

        vcProductDao.insert(vcProduct);

        int seed = new Random().nextInt(10);
        while ( seed ==0  ) {
            seed = new Random().nextInt(10);
        }

        for (Item skuItem : skuItems) {
            VCItem vcItem = new VCItem();
            vcItem.setId(IdentifierGen.uuid());
            vcItem.setProductCode(spu.getCode());
            vcItem.setOriginalCode(skuItem.getCode());
            vcItem.setSkuId("");
            vcItem.setUpc(""); // UPC编码（只包含字母和数字）


            vcItem.setSkuName(String.format("%s", skuItem.getName())); // XXX衬衫	商品名称
            vcItem.setItemNum(CodeEncoder.encode(skuItem.getCode(), seed)); // 132 货号（货号不能包括中文，只能为英文或数字）

            vcItem.setOriginalCode(skuItem.getCode());
            vcItem.setProductCode(skuItem.getProductcode());

            vcItem.setWeight("1"); // 	1.123	商品重量(单位：千克，含包装)小数点后保留3位
            vcItem.setLength("1"); // 	1.123	长度(单位：毫米，含包装)
            vcItem.setWidth("1"); // 	1.123	宽度(单位：毫米，含包装)
            vcItem.setHeight("1"); // 	1.123	高度(单位：毫米，含包装)

            HtmlCache htmlCache = htmlCacheDao.load(skuItem.getUrl());
            if ( htmlCache==null  ) {
                htmlCache = new HtmlCache();
            }

            try {
                SkuItem _skuItem = parser.parser(skuItem.getUrl(), htmlCache.getHtml());
                // %s %s %s (%s)【预计%s出货】
                // 品牌 制造商型号 标题 (数量)【预计8天出货】
                // 京苏 309磨料，100*25*6，180#-1 309金世博磨料Φ100带柄页轮（1个） 【预计8天出货】
                String title = _skuItem.getTitle();
                String[] attrs = _skuItem.getModel().split("，");
                if ( attrs!=null ) {
                    for (String attr : attrs) {
                        if ( StringUtils.isNotBlank(attr) ) {
                            title = title.replace(attr, "");
                        }
                    }
                }


                String brand = vcProduct.getBrandName();

                vcItem.setSkuName(String.format("%s %s %s【预计%s出货】", (brand==null ? "": brand), _skuItem.getModel(), title, _skuItem.getFahuori())); // XXX衬衫	商品名称

                int newScale = 1;
                float price = _skuItem.getOriginalPrice();
                if ( price > 50 ) {
                    newScale = 0;
                }

                BigDecimal purchasePrice = new BigDecimal(price * 1.05 ).setScale(newScale, RoundingMode.HALF_DOWN);
                BigDecimal memberPrice = purchasePrice.multiply(new BigDecimal(1.17)).setScale(newScale, RoundingMode.HALF_DOWN);;
                BigDecimal marketPrice = new BigDecimal(price * 1.38).setScale(newScale, RoundingMode.HALF_DOWN);

                vcItem.setMarketPrice(marketPrice.stripTrailingZeros().toPlainString()); // 	900	市场价（小数点后保留2位）
                vcItem.setPurchasePrice(purchasePrice.stripTrailingZeros().toPlainString()); // 700	采购价（小数点后保留2位）
                vcItem.setMemberPrice(memberPrice.stripTrailingZeros().toPlainString()); // 700	800	京东价（小数点后保留2位）
            } catch (Exception e) {
                LOG.warn("[URL: " + skuItem.getUrl() + "]解析失败: " + e.getMessage());
            }

            try {
                SQLExecutor.insert(vcItem);
            } catch (Exception e) {
                e.printStackTrace();
            }
        }


    }


    public static void main(String[] args) {

        Map<String, Integer> map = new ConcurrentHashMap<>();
        System.out.println(map.remove("1"));
    }

    /**
     * XSSFBuiltinTableStyle
     */
    private static void applyTableStyle(XSSFSheet sheet, XSSFBuiltinTableStyle tableStyle, String start, String end, int colnum){

        //  < v4.0.0
        XSSFTable table = sheet.createTable();

// >= 4.0.0
//        XSSFTable table = sheet.createTable(null);
        CTTable cttable = table.getCTTable();

        cttable.setDisplayName("table_" + start);
        cttable.setId(sheet.getSheetName().hashCode());
        cttable.setName("name_" + start);

        System.out.println(start+ ":" + end + " => " + tableStyle.name());

        cttable.setRef(start+ ":" + end);
        cttable.setTotalsRowShown(false);

        CTTableStyleInfo styleInfo = cttable.addNewTableStyleInfo();
        styleInfo.setName(tableStyle.name());
        styleInfo.setShowColumnStripes(false);
        styleInfo.setShowRowStripes(true);

        CTTableColumns columns = cttable.addNewTableColumns();
        columns.setCount(colnum);
        for (int i = 1; i <= colnum; i++) {
            CTTableColumn column = columns.addNewTableColumn();
            column.setId(sheet.getSheetName().hashCode() + i);
            column.setName("table_column_" + start + i);
        }

    }
}
